﻿using System;
using System.Collections.Generic;

using System.Text;
using Itegracion.Framework.Forms;
using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Data.Common;
using System.Data;

namespace Itegracion.iAlmacen.Model
{
    public class Documento
    {
        public static List<Element> TraerListado(List<Element> entrada)
        {
            List<Element> rpta = new List<Element>();

            Database db = DatabaseFactory.CreateDatabase("iAlmacen");
            string txt = string.Format(@"
                SELECT
                  d.id,
                  d.fecha,
                  d.codigo,
                  c.nombre as tipo,
                  eo.nombre as origen,
                  ed.nombre as destino
                FROM
                  Documento d
                  JOIN Constante c ON c.codigo = 'TipoDocumento' AND d.idTipo = c.oid
                  LEFT JOIN Relacion ro ON d.id = ro.idDocumento and ro.idTipo = 1
                  LEFT JOIN Entidad eo ON ro.idEntidad = eo.id
                  LEFT JOIN Relacion rd ON d.id = rd.idDocumento and rd.idTipo = 2
                  LEFT JOIN Entidad ed ON rd.idEntidad = ed.id
                WHERE
                  d.idTipo IN ({0})
                ORDER BY
                  d.fecha DESC", entrada[0].Value);
            
            DbCommand command = db.GetSqlStringCommand(txt);
            DataTable dt = db.ExecuteDataSet(command).Tables[0];
            rpta.Add(new Element("rpta", dt));

            return rpta;
        }
        public static List<Element> Traer(List<Element> entrada)
        {
            List<Element> rpta = new List<Element>();

            Database db = DatabaseFactory.CreateDatabase("iAlmacen");
            DbCommand command = db.GetSqlStringCommand(@"
                SELECT
                  d.id,
                  d.fecha,
                  d.codigo,
                  d.idTipo,
                  eo.docId,
                  eo.nombre as origen,
                  eo.direccion as odireccion
                FROM
                  Documento d
                  LEFT JOIN Relacion ro ON d.id = ro.idDocumento and ro.idTipo = 2
                  LEFT JOIN Entidad eo ON ro.idEntidad = eo.id
                WHERE
                  d.id = @id");
            db.AddInParameter(command, "id", DbType.Int32, entrada[0].Value);
            DataTable dt = db.ExecuteDataSet(command).Tables[0];
            rpta.AddRange(dt.Rows.ToList());

            command = db.GetSqlStringCommand(@"
                SELECT
                  di.cantidad, p.id, p.nombre, di.precio_unitario, di.idDocumento
                FROM
                  DocumentoItem di
                  JOIN Producto p ON di.idProducto = p.id
                WHERE
                  di.idDocumento = @id");
            db.AddInParameter(command, "id", DbType.Int32, entrada[0].Value);
            dt = db.ExecuteDataSet(command).Tables[0];
            rpta.Add(new Element("rpta", dt));

            return rpta;
        }
    }
}
